//
//  RMDataManager.swift
//  TestDemo
//
//  Created by xiong on 2025/8/28.
//

import UIKit
import FMDB
class RMDataManager: NSObject {
    static let shared = RMDataManager()
    private var dbQueue: FMDatabaseQueue?
    
    let userTName = "user"
    let messageTName = "message"

    private override init() {
        super.init()
        openDatabase()
        createUserTable()
    }
    
    /// 数据库路径
    private func databasePath() -> String {
        let docPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
        return (docPath as NSString).appendingPathComponent("app_database.sqlite")
    }
    
    /// 打开数据库
    private func openDatabase() {
        dbQueue = FMDatabaseQueue(path: databasePath())
    }
    
    /// 创建表
    private func createUserTable() {
        let userTable = """
            CREATE TABLE IF NOT EXISTS \(userTName) ('userId' VARCHAR PRIMARY KEY  NOT NULL  UNIQUE , 'userNickname' VARCHAR,'headimgurl' VARCHAR,'account' VARCHAR,'remarkName' VARCHAR, 'describe' VARCHAR, 'role' VARCHAR,'createUserId' VARCHAR, 'userDescription' VARCHAR, 'userHead' VARCHAR,'roomFlag' INTEGER DEFAULT 0,'category' INTEGER DEFAULT 0, 'content' VARCHAR,'type' INTEGER,'timeSend' VARCHAR,'objectId' VARCHAR,'fromId' VARCHAR,'fromUserName' VARCHAR,'downloadTime' VARCHAR,'timeCreate' VARCHAR,'myJoinTime' DATETIMR,'joinSeqNo' VARCHAR,'newMsgs' INTEGER, 'status' INTEGER DEFAULT 1, 'userType' INTEGER DEFAULT 0, 'companyId' INTEGER, 'isMySend' INTEGER,'roomId' VARCHAR,'showRead' INTEGER,'showMember' INTEGER,'allowSendCard' INTEGER,'allowInviteFriend' INTEGER,'allowUploadFile' INTEGER,'allowConference' INTEGER,'allowSpeakCourse' INTEGER,'isNeedVerify' INTEGER,'lastInput' VARCHAR,'topTime' VARCHAR,'encryptType' INTEGER,'groupStatus' INTEGER DEFAULT 0,'isOnLine' INTEGER,'isOpenReadDel' INTEGER,'isReadDel' INTEGER,'isSendMsgState' INTEGER,'isAutoSpeech' INTEGER,'isSendRecipt' INTEGER,'isDevice' INTEGER,'chatRecordTimeOut' VARCHAR,'offlineNoPushMsg' INTEGER,'isUseNewsExpress' INTEGER,'isAtMe' INTEGER,'isMenbersFollow' INTEGER,'talkTime' VARCHAR,'memberTalkTime' VARCHAR,'joinTime' VARCHAR,'publicKeyDH' VARCHAR,'publicKeyRSARoom' VARCHAR,'chatKeyGroup' VARCHAR,'isSecretGroup' INTEGER,'isLostChatKeyGroup' INTEGER,'maxSeqNo' VARCHAR,'loseSeqNos' VARCHAR,'isHideSession' BOOL DEFAULT FALSE,'hiding' VARCHAR,'roomRole' INTEGER,'deleteMsgListTime' VARCHAR ,'recommendedGroup' INTEGER ,'showMarker' INTEGER,'isSeq' INTEGER,'groupOfflineStartSeq' INTEGER,'groupOfflineEndSeq' INTEGER,'groupStartTime' VARCHAR,'groupLastTime' VARCHAR );
            """
        
        
        dbQueue?.inDatabase { db in
            do {
                try db.executeUpdate(userTable, values: nil)
            } catch {
                print("创建表失败: \(error.localizedDescription)")
            }
        }
    }
    
    // MARK: - User
    
    /// 插入用户
    func insertUser(_ user: RMUserModel) {
        let sql = "INSERT OR REPLACE INTO \(userTName) ('userId','userNickname','account','remarkName','role','createUserId','userDescription','userHead','roomFlag','category','timeCreate','joinSeqNo','newMsgs','status','userType','companyId','type','content','isMySend','roomId','timeSend','objectId','fromId',fromUserName,'downloadTime','lastInput','showRead','showMember','allowSendCard','allowInviteFriend','allowUploadFile','allowConference','allowSpeakCourse','isNeedVerify','topTime','encryptType','groupStatus','isOnLine','isOpenReadDel','isSendMsgState','isSendRecipt','isDevice','chatRecordTimeOut','offlineNoPushMsg','isAtMe','isMenbersFollow','talkTime','memberTalkTime','publicKeyDH','publicKeyRSARoom','chatKeyGroup','isSecretGroup','isLostChatKeyGroup','maxSeqNo','loseSeqNos','isHideSession','hiding','roomRole') VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        dbQueue?.inDatabase { db in
            do {
                try db.executeUpdate(sql, values: [user.userId,user.userNickname, user.account,user.remarkName,user.role,user.createUserId, user.userDescription,user.userHead,user.roomFlag,user.category, user.timeCreate,user.joinSeqNo,user.msgsNew,user.status, user.userType,user.companyId,user.type,user.content,user.isMySend, user.roomId,user.timeSend,user.objectId,user.from,user.fromUserName,user.downloadTime,user.lastInput,user.showRead, user.showMember,user.allowSendCard,user.allowInviteFriend,user.allowUploadFile,user.allowConference,user.allowSpeakCourse,user.isNeedVerify,user.topTime,user.encryptType,user.groupStatus,user.isOnLine,user.isOpenReadDel,user.isSendMsgState,user.isSendRecipt,user.isDevice,user.chatRecordTimeOut,user.offlineNoPushMsg,user.isAtMe,user.isMenbersFollow,user.talkTime,user.memberTalkTime,user.publicKeyDH,user.publicKeyRSARoom,user.chatKeyGroup,user.isSecretGroup,user.isLostChatKeyGroup,user.maxSeqNo,user.loseSeqNos,user.isHideSession,user.hiding,user.roomRole])
            } catch {
                print("插入用户失败: \(error.localizedDescription)")
            }
        }
    }
    
    /// 查询所有用户
    func fetchUsers() -> [RMUserModel] {
        var users: [RMUserModel] = []
        let sql = "SELECT * FROM \(userTName)"
        dbQueue?.inDatabase { db in
            if let rs = try? db.executeQuery(sql, values: nil) {
                while rs.next() {
                    let user = RMUserModel()
                    let mirror = Mirror(reflecting: user)
                    for child in mirror.children {
                        if let propertyName = child.label {
                            // 获取列值
                            if let columnValue = rs.object(forColumn: propertyName) {
                                user.setValue(columnValue, forKey: propertyName)
                            }
                        }
                    }
                                    
                    users.append(user)
                }
                rs.close()
            }
        }
        return users
    }
    
    // MARK: - Message
    func messageTableName(forFriendId friendId: String) -> String {
        return "messages_\(friendId)"
    }
    func createMessageTable(forFriendId friendId: String) {
        let tableName = messageTableName(forFriendId: friendId)
        let sql = """
        CREATE TABLE IF NOT EXISTS \(tableName) (
        'messageNo' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  UNIQUE , 
        'fromUserId' VARCHAR, 
        'toUserId' VARCHAR, 
        'content' VARCHAR, 
        'timeSend' VARCHAR,
        'timeReceive' VARCHAR,
        'type' INTEGER, 
        'messageId' VARCHAR,
        'speechMsg' VARCHAR,
        'fileData' VARCHAR, 
        'fileName' VARCHAR,
        'fileSize' INTEGER,
        'location_x' INTEGER,
        'location_y' INTEGER,
        'timeLen' INTEGER,
        'isRead' INTEGER,
        'isSend' INTEGER,
        'objectId' INTEGER,
        'isReceive' INTEGER,
        'isUpload' INTEGER,
        'fromUserName' VARCHAR,
        'toUserName' VARCHAR,
        'isReadDel' INTEGER,
        'fromId' VARCHAR,
        'toId' VARCHAR,
        'readPersons' INTEGER,
        'showRead' INTEGER,
        'readTime' VARCHAR, 
        'deleteTime' VARCHAR,
        'chatMsgHeight' VARCHAR,
        'isShowTime' INTEGER,
        'signature' VARCHAR,
        'isVerifySignatureFailed' INTEGER,
        'generalMark' INTEGER,
        'isEncrypt' INTEGER,
        'seqNo' INTEGER DEFAULT 1,
        'srvId' VARCHAR)
        """
        
        dbQueue?.inDatabase { db in
            do {
                try db.executeUpdate(sql, values: nil)
            } catch {
                print("创建消息表失败: \(error.localizedDescription)")
            }
        }
    }
    /// 插入消息
    func insertMessage(_ message: RMMessageModel, userId:String) {
        let tableName = messageTableName(forFriendId: userId)
        let sql = "INSERT INTO \(tableName) (fromUserId,toUserId,content,type,messageId,timeSend,timeReceive,speechMsg,fileName,fileSize,location_x,location_y,timeLen,isRead,isSend,objectId,isReceive,isUpload,fromUserName,toUserName,isReadDel,fromId,toId,readPersons,showRead,readTime,deleteTime,chatMsgHeight,isShowTime,signature,isVerifySignatureFailed,generalMark,isEncrypt,seqNo,srvId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        dbQueue?.inDatabase { db in
            do {
                try db.executeUpdate(sql, values: [message.fromUserId,message.toUserId,message.content,message.type,message.messageId,message.timeSend,message.timeReceive,message.speechMsg,message.fileName,message.fileSize,message.location_x,message.location_y,message.timeLen,message.isRead,message.isSend,message.objectId,message.isReceive,message.isUpload,message.fromUserName,message.toUserName,message.isReadDel,message.fromId,message.toId,message.readPersons,message.showRead,message.readTime,message.deleteTime,message.chatMsgHeight,message.isShowTime,message.signature,message.isVerifySignatureFailed,message.generalMark,message.isEncrypt,message.seqNo,message.srvId])
            } catch {
                print("插入消息失败: \(error.localizedDescription)")
            }
        }
    }
    
    /// 查询某个用户的消息
    func fetchMessages(forUserId userId: String) -> [RMMessageModel] {
        let tableName = messageTableName(forFriendId: userId)
        var messages: [RMMessageModel] = []
        
        let sql = "SELECT * FROM \(tableName) WHERE fromUserId = ? OR toUserId = ? ORDER BY timestamp ASC"
        
        dbQueue?.inDatabase { db in
            if let rs = try? db.executeQuery(sql, values: [userId, userId]) {
                while rs.next() {
                    let message = RMMessageModel()
                    let mirror = Mirror(reflecting: message)
                    for child in mirror.children {
                        if let propertyName = child.label {
                            // 获取列值
                            if let columnValue = rs.object(forColumn: propertyName) {
                                message.setValue(columnValue, forKey: propertyName)
                            }
                        }
                    }
                                    
                    messages.append(message)
                }
                rs.close()
            }
        }
        return messages
    }
}
